//
//  SQLiteManager.swift
//  SQLite3演练
//
//  Created by 杨兵 on 15/5/26.
//  Copyright (c) 2015年 bing. All rights reserved.
//

import UIKit

class SQLiteManager: NSObject {
    
    // 创建数据访问单例
    private static let instance = SQLiteManager()
    class func sharedSQLiteManager() -> SQLiteManager {
        return instance
    }
    
    
    // 获取全局的数据库指针变量
    private var db: COpaquePointer = nil
    
    func openDB(dbName: String) -> Bool {
        
        // 数据库完整路径
        let filePath = NSSearchPathForDirectoriesInDomains(NSSearchPathDirectory.DocumentDirectory, NSSearchPathDomainMask.UserDomainMask, true).last?.stringByAppendingPathComponent(dbName)
        println(filePath!)

        // 打开数据库,没有就会创建
        if sqlite3_open(filePath!.cStringUsingEncoding(NSUTF8StringEncoding)!, &db) != SQLITE_OK {
            println("数据库连接失败")
            return false
        }
        
        println("数据库连接成功了")
        return true
    }
    
    
    // 执行sql语句
    func execSQL(sql: String) -> Bool {
        
        // 转为c字符串
        let cstr = sql.cStringUsingEncoding(NSUTF8StringEncoding)!
        
        // 执行查询
        return sqlite3_exec(db, cstr, nil, nil, nil) == SQLITE_OK
    }
    
    
    // 查询数据
    func querydatas(sql: String) -> [[String: AnyObject]]? {
        let csql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
        // 编译后的语句流对象
        var stmt: COpaquePointer = nil
        
        /**
            参数:
                1.数据库句柄,
                2.sql
                3.一字节为单位的SQL的最大长度,传入 -1 会自动计算
                4.sql的语句地址
                5.为使用的指针地址,通常传入nil
        */
        var result:[[String: AnyObject]]?
        if sqlite3_prepare_v2(db, csql!, -1, &stmt, nil) == SQLITE_OK {
            
            result = [[String: AnyObject]]()
            // 判断是否读取到了记录
            while sqlite3_step(stmt) == SQLITE_ROW {
                result?.append(transformOneRecord(stmt)!)
                
            }
            
        }
        
        // 注意,释放语句对象
        sqlite3_finalize(stmt)
        return result
        
    }
    
    // 转换一条记录
    private func transformOneRecord(stmt: COpaquePointer) -> [String: AnyObject]? {
        
        // 处理行数据,获取每行的列数
        let colCount = sqlite3_column_count(stmt)
        // 获取值
        var record = [String: AnyObject]()
        for i in 0..<colCount {
            
            // 数据类型
            let type = sqlite3_column_type(stmt, i)
            
            // 字段名称
            let name = sqlite3_column_name(stmt, i)
            let colname = String(CString: name, encoding: NSUTF8StringEncoding)!
            
            switch type {
            case SQLITE_INTEGER:
                let i = sqlite3_column_int64(stmt, i)
                record[colname] = Int(i)
            case SQLITE_FLOAT:
                let f = sqlite3_column_double(stmt, i)
                record[colname] = f
            case SQLITE3_TEXT:
                let t = UnsafePointer <Int8>(sqlite3_column_text(stmt, i))
                record[colname] = String(CString: t, encoding: NSUTF8StringEncoding)
            case SQLITE_NULL:
                record[colname] = NSNull()
            default:
                println("不支持的类型")
            }
        }
        
        return record
    }
    
    // 开启事务
    func beginTransaction() -> Bool {
        return execSQL("BEGIN TRANSACTION;")
    }
    
    // 提交事务
    func commitTransaction() -> Bool {
        return execSQL("COMMIT TRANSACTION;")
    }
    
    // 回滚事务
    func rollbackTransaction() -> Bool {
        return execSQL("ROLLBACK TRANSACTION;")
    }
    
    
    // 使用预编译语句批量更新
    // 可变参数 CVarArgType
    // 为了不出现乱码,字符串绑定的时候使用
    private let SQLITE_TRANSIENT = sqlite3_destructor_type(COpaquePointer(bitPattern: -1))
    func batchUpdate(sql: String, params: CVarArgType...) -> Bool {
        
        var stmt: COpaquePointer = nil
        
        if sqlite3_prepare_v2(db, sql, -1, &stmt, nil) == SQLITE_OK {
            // 绑定参数
            var index: Int32 = 1
            for obj in params {
                if obj is Int {
                    sqlite3_bind_int64(stmt, index, sqlite3_int64(obj as! Int))
                } else if obj is Double {
                    sqlite3_bind_double(stmt, index, obj as! Double)
                } else if obj is String {
                    let str = (obj as! String).cStringUsingEncoding(NSUTF8StringEncoding)!
                    sqlite3_bind_text(stmt, index, str, -1, SQLITE_TRANSIENT)
                } else if obj is NSNull {
                    sqlite3_bind_null(stmt, index)
                }
                index++
            }
        }
        var result = true
        // 单步执行
        if sqlite3_step(stmt) != SQLITE_DONE {
            println("插入错误")
            result = false
        }
        // 语句复位
        if sqlite3_reset(stmt) != SQLITE_OK {
            println("复位错误")
            result = false
        }
        sqlite3_finalize(stmt)
        
        return result
    }
    
}
